package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTables {

	/**
	 * @param args
	 */
	public Boolean CreateTablesMethod(String path) {

			
			try {
			
				Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

                Connection conn = DriverManager.getConnection("jdbc:derby:"+path+";create=true");

                Statement command = conn.createStatement();
                
                
				// Bird table - ok
				try {
					command.executeUpdate(
							"CREATE TABLE Bird (scientific_name VARCHAR(45) NOT NULL, "+
							"size INT,"+
							"risk_of_extinction VARCHAR(45) NOT NULL,"+
							"weight INT,"+
							"general_information VARCHAR(500), "+
							"map VARCHAR(100) NOT NULL,"+
							"PRIMARY KEY (scientific_name))");
					
					System.out.println("Table Bird created succesfully");
				}
					catch (SQLException err){
						System.out.println("Can't create Bird table: " + err.getMessage());
					}
					
				
				// Food table - ok
				try {
					command.executeUpdate(
							"CREATE TABLE Food (type_of_food VARCHAR(100) NOT NULL, "+
							"Bird_scientific_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (type_of_food,Bird_scientific_name), FOREIGN KEY (Bird_scientific_name) "+
							"REFERENCES Bird (scientific_name) "+
							"ON DELETE NO ACTION " +
							"ON UPDATE NO ACTION)");
					
					System.out.println("Table Food created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Food table: " + err.getMessage());
	
				}
	
				// Habitat table - wip
				try {
					command.executeUpdate(
							"CREATE TABLE Habitat (precipitation VARCHAR(45), "+
							"summer_temperature_average INT, "+
							"winter_temperature_average INT, "+
							"habitat_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (habitat_name) )");
					
					System.out.println("Table Habitat created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Habitat table: " + err.getMessage());
	
				}
	
				// Bird_has_Habitat table
				try {
					command.executeUpdate(
							"CREATE TABLE Bird_has_Habitat (Bird_scientific_name VARCHAR(45) NOT NULL, "+
							"Habitat_habitat_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (Bird_scientific_name, Habitat_habitat_name), "+
							"FOREIGN KEY (Bird_scientific_name ) "+
							"REFERENCES Bird (scientific_name ) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION, "+
							"FOREIGN KEY (Habitat_habitat_name) "+
							"REFERENCES Habitat (habitat_name) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION)");
				
					System.out.println("Table Bird_has_Habitat created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Bird_has_Habitat table: " + err.getMessage());
	
				}
	
				// Pictures table - ok
				try {
					command.executeUpdate(
							"CREATE TABLE Pictures (idPicture INT NOT NULL, "+
							"Bird_scientific_name VARCHAR(45) NOT NULL, "+
							"date DATE, "+
							"PRIMARY KEY (idPicture), "+
							"FOREIGN KEY (Bird_scientific_name) "+
							"REFERENCES Bird (scientific_name) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION)");
				
					System.out.println("Table Pictures created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Pictures table: " + err.getMessage());
	
				}
	
				// Popular_Names table - ok
				try {
					command.executeUpdate(
							"CREATE TABLE Popular_Names (name VARCHAR(100) NOT NULL, "+
							"Bird_scientific_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (name, Bird_scientific_name), "+
							"FOREIGN KEY (Bird_scientific_name) "+
							"REFERENCES Bird (scientific_name) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION)");
				
					System.out.println("Table Popular_Names created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Popular_Names table: " + err.getMessage());
	
				}
	
				// Schematic_Model table
				try {
					command.executeUpdate(
							"CREATE TABLE Schematic_Model (wings VARCHAR(45) NOT NULL, "+
							"head VARCHAR(45) NOT NULL, "+
							"tail VARCHAR(45) NOT NULL, "+
							"body VARCHAR(45) NOT NULL, "+
							"crest VARCHAR(45) NOT NULL, "+
							"Bird_scientific_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (Bird_scientific_name), "+
							"FOREIGN KEY (Bird_scientific_name) "+
							"REFERENCES Bird (scientific_name) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION)");
				
					System.out.println("Table Schematic_Model created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Schematic_Model table: " + err.getMessage());
	
				}
	
				// Worker table
				try {
					command.executeUpdate(
							"CREATE TABLE Worker (Worker_id VARCHAR(45) NOT NULL, "+
							"password VARCHAR(13) NOT NULL, "+
							"name VARCHAR(45) NOT NULL, "+
							"CPF INT NOT NULL, "+
							"birth_date TIMESTAMP, "+
							"terminal_serial_identification INT NOT NULL, "+
							"PRIMARY KEY (Worker_id))");
				
					System.out.println("Table Worker created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create Worker table: " + err.getMessage());
	
				}
	
				// State table State
				try {
					command.executeUpdate(
							"CREATE TABLE State (state_name VARCHAR(45) NOT NULL, PRIMARY KEY (state_name))");
				
					System.out.println("Table State created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create State table: " + err.getMessage());
	
				}
	
				// State_has_Habitat table wip
				try {
					command.executeUpdate(
							"CREATE TABLE State_has_Habitat (State_state_name varchar(45) NOT NULL, "+
							"Habitat_habitat_name VARCHAR(45) NOT NULL, "+
							"PRIMARY KEY (State_state_name, Habitat_habitat_name), "+
							"FOREIGN KEY (State_state_name) "+
							"REFERENCES State (state_name) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION, "+
							"FOREIGN KEY (Habitat_habitat_name) "+
							"REFERENCES Habitat (habitat_name ) "+
							"ON DELETE NO ACTION "+
							"ON UPDATE NO ACTION)");
				
					System.out.println("Table State_Has_Habitat created succesfully");
				}
				catch (SQLException err){
					System.out.println("Can't create State_has_Habitat table: " + err.getMessage());
	
				}
	
				try {
					command.close();
					conn.close();
				} catch (SQLException e) {
					System.out.println("Error on close: "+e);
					e.printStackTrace();
				}
				
				}catch(ClassNotFoundException errclass){
	                System.out.println("Missing class! Check your CLASSPATH");
				
				} catch (SQLException e) {
					System.out.println("Error: "+e);
					e.printStackTrace();
				}
			
	

		System.out.println("Tables successfully created.");
		return true;
	}


}
